﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESProblemBusiness
    {
        #region 获取问题列表
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel(Dictionary<string, string> para)
        {
            string strSQL = GetSQL_D(para);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("SELECT pi.problemtitle,pt.problemtypename,pt.problemtypeid,l.problemlevelid,");
            strSQL.AppendLine("       l.problemlevelname,pi.submitdate,f.factoryname AS tofactoryname,");
            strSQL.AppendLine("       pi.tofactoryid,e.fullname AS tofullname,pi.toemployeeid,pi.id,pi.problemdetails,");
            strSQL.AppendLine("       pi.planneddisposedate,e2.fullname AS disposefullname,pi.disposedate,");
            strSQL.AppendLine("       pi.status,DECODE(pi.status, 0, '未处理', 5, '已指派', 10, '已处理', 20, '已关闭') AS statusdisp,");
            strSQL.AppendLine("       c.containername,c.containerid,sb.specname,pi.tofactoryid2,pi.toemployeeid2,pi.problemresult,");
            strSQL.AppendLine("       f2.factoryname AS tofactoryname2,e3.fullname AS tofullname2,pi.specid,pi.notes2");
            strSQL.AppendLine("FROM probleminfo pi");
            strSQL.AppendLine("LEFT JOIN container c ON c.containerid = pi.containerid");
            strSQL.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strSQL.AppendLine("LEFT JOIN product p ON p.productid = pi.productid");
            strSQL.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strSQL.AppendLine("LEFT JOIN spec s ON s.specid = pi.specid");
            strSQL.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strSQL.AppendLine("LEFT JOIN problemtype pt ON pt.problemtypeid = pi.problemtypeid");
            strSQL.AppendLine("LEFT JOIN problemlevel l ON l.problemlevelid = pi.problemlevelid");
            strSQL.AppendLine("LEFT JOIN factory f ON f.factoryid = pi.tofactoryid");
            strSQL.AppendLine("LEFT JOIN factory f2 ON f2.factoryid = pi.tofactoryid2");
            strSQL.AppendLine("LEFT JOIN employee e ON e.employeeid = pi.toemployeeid");
            strSQL.AppendLine("LEFT JOIN employee e3 ON e3.employeeid = pi.toemployeeid2");
            strSQL.AppendLine("LEFT JOIN employee e2 ON e2.employeeid = pi.disposeemployeeid");
            strSQL.AppendLine("WHERE 1 = 1");

            if (para.Keys.Contains("Status"))
            {
                if (!string.IsNullOrEmpty(para["Status"]))
                {
                    strSQL.AppendLine(string.Format("AND pi.status IN ({0})", para["Status"]));
                }
            }
            if (para.Keys.Contains("ContainerID"))
            {
                if (!string.IsNullOrEmpty(para["ContainerID"]))
                {
                    strSQL.AppendLine(string.Format("AND (pi.containerid = '{0}' OR pi.containerid IS NULL)", para["ContainerID"]));
                }
            }
            if (para.Keys.Contains("ScanContainerName"))
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strSQL.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("SubmitEmployeeID"))
            {
                if (!string.IsNullOrEmpty(para["SubmitEmployeeID"]))
                {
                    strSQL.AppendLine(string.Format("AND pi.submitemployeeid = '{0}'", para["SubmitEmployeeID"]));
                }
            }
            if (para.Keys.Contains("ProblemTypeID"))
            {
                if (!string.IsNullOrEmpty(para["ProblemTypeID"]))
                {
                    strSQL.AppendLine(string.Format("AND pi.problemtypeid = '{0}'", para["ProblemTypeID"]));
                }
            }
            if (para.Keys.Contains("ProblemLevelID"))
            {
                if (!string.IsNullOrEmpty(para["ProblemLevelID"]))
                {
                    strSQL.AppendLine(string.Format("AND pi.problemlevelid = '{0}'", para["ProblemLevelID"]));
                }
            }
            if (para.Keys.Contains("ToFactoryID"))
            {
                if (!string.IsNullOrEmpty(para["ToFactoryID"]))
                {
                    strSQL.AppendLine(string.Format("AND pi.tofactoryid = '{0}'", para["ToFactoryID"]));
                }
            }
            if (para.Keys.Contains("ToEmployeeID"))
            {
                if (!string.IsNullOrEmpty(para["ToEmployeeID"]))
                {
                    strSQL.AppendLine(string.Format("AND pi.toemployeeid = '{0}'", para["ToEmployeeID"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strSQL.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strSQL.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strSQL.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("SpecName")) //工序
            {
                if (!string.IsNullOrEmpty(para["SpecName"]))
                {
                    strSQL.AppendLine(string.Format("AND LOWER(sb.specname) LIKE '%{0}%'", para["SpecName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strSQL.AppendLine(string.Format("AND pi.planneddisposedate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strSQL.AppendLine(string.Format("AND pi.planneddisposedate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            if (para.Keys.Contains("ProblemTitle")) //标题
            {
                if (!string.IsNullOrEmpty(para["ProblemTitle"]))
                {
                    strSQL.AppendLine(string.Format("AND LOWER(pi.problemtitle) LIKE '%{0}%'", para["ProblemTitle"].ToLower()));
                }
            }

            if (para.Keys.Contains("SearchEmployee"))
            {
                if (!string.IsNullOrEmpty(para["SearchEmployee"]))
                {
                    strSQL.AppendLine(string.Format("AND （pi.toemployeeid = '{0}' OR pi.toemployeeid2 = '{0}'）", para["SearchEmployee"]));
                }
            }

            strSQL.AppendLine("ORDER BY pi.submitdate ASC");

            return strSQL.ToString();
        }
        #endregion
        #endregion

        #region 指派问题
        public Boolean DispatchProblem(Dictionary<string, string> para)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE probleminfo SET");
            strSQL.AppendLine(string.Format("DispatchEmployeeID = '{0}',", para["DispatchEmployeeID"]));
            strSQL.AppendLine(string.Format("DispatchDate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["DispatchDate"]));
            strSQL.AppendLine(string.Format("Notes2 = '{0}',", para["Notes2"]));
            strSQL.AppendLine(string.Format("ToFactoryID2 = '{0}',", para["ToFactoryID2"]));
            strSQL.AppendLine(string.Format("ToEmployeeID2 = '{0}',", para["ToEmployeeID2"]));
            strSQL.AppendLine("Status = 5");
            strSQL.AppendLine(string.Format("WHERE id = '{0}'", para["ID"]));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 处理问题
        public Boolean DisposeProblem(Dictionary<string, string> para)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE probleminfo SET");
            strSQL.AppendLine(string.Format("DisposeEmployeeID = '{0}',", para["DisposeEmployeeID"]));
            strSQL.AppendLine(string.Format("DisposeDate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["DisposeDate"]));
            strSQL.AppendLine(string.Format("ProblemResult = '{0}',", para["ProblemResult"]));
            strSQL.AppendLine("Status = 10");
            strSQL.AppendLine(string.Format("WHERE id = '{0}'", para["ID"]));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 更新问题状态
        public Boolean ChangeProblemStatus(string strID, int intStatus)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("UPDATE probleminfo SET status = {1} WHERE id = '{0}'", strID, intStatus));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 删除问题记录
        public Boolean DeleteProblemInfo(string strID)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("DELETE probleminfo WHERE id = '{0}'", strID));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 更新问题记录
        public Boolean UpdateProblemInfo(string strID, Dictionary<string, string> para)
        {
            int intCount = 0;
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("UPDATE probleminfo SET");

            if (para.Keys.Contains("ProblemTitle"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ProblemTitle = '{0}'", para["ProblemTitle"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ContainerID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ContainerID = '{0}'", para["ContainerID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ProductID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ProductID = '{0}'", para["ProductID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("SpecID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("SpecID = '{0}'", para["SpecID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ResourceID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ResourceID = '{0}'", para["ResourceID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ProblemTypeID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ProblemTypeID = '{0}'", para["ProblemTypeID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("FactoryID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("FactoryID = '{0}'", para["FactoryID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ToFactoryID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ToFactoryID = '{0}'", para["ToFactoryID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ToEmployeeID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ToEmployeeID = '{0}'", para["ToEmployeeID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ProblemLevelID"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ProblemLevelID = '{0}'", para["ProblemLevelID"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("ProblemDetails"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("ProblemDetails = '{0}'", para["ProblemDetails"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("Status"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("Status = {0}", para["Status"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("Notes"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("Notes = '{0}'", para["Notes"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }
            if (para.Keys.Contains("PlannedDisposeDate"))
            {
                intCount++;
                strSQL.AppendLine(string.Format("PlannedDisposeDate = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["PlannedDisposeDate"]));

                if (intCount < para.Count)
                {
                    strSQL.AppendLine(",");
                }
            }

            strSQL.AppendLine(string.Format("WHERE id = '{0}'", strID));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 添加问题记录
        /// <summary>
        /// 添加问题记录
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public Boolean AddProblemInfo(Dictionary<string, string> para)
        {
            //主表
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO probleminfo(ID,problemtitle,submitemployeeid,submitdate,containerid,");
            strSQL.AppendLine("     productid,specid,resourceid,problemtypeid,factoryid,tofactoryid,toemployeeid,");
            strSQL.AppendLine("     problemlevelid,problemdetails,status,notes,planneddisposedate)");
            strSQL.AppendLine("VALUES (");
            strSQL.AppendLine(string.Format("'{0}',", para["ID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ProblemTitle"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SubmitEmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["SubmitDate"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ContainerID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ProductID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SpecID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ResourceID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ProblemTypeID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["FactoryID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ToFactoryID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ToEmployeeID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ProblemLevelID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ProblemDetails"]));
            strSQL.AppendLine(string.Format("{0},", para["Status"]));
            strSQL.AppendLine(string.Format("'{0}',", para["Notes"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", para["PlannedDisposeDate"]));
            strSQL.AppendLine(")");

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion
    }
}
